<?php
/**
 * Created by PhpStorm.
 * User: linuxwu
 * Date: 2019/4/15
 * Time: 9:22 AM
 */
namespace App\Http\Controllers\Home;
use App\Model\Area;
use App\Model\Checkcategory;
use App\Model\Comcheckagain;
use App\Model\Comcheckagaindetail;
use App\User;
use Illuminate\Support\Facades\Auth;
use PhpOffice\PhpWord\TemplateProcessor;
use App\Http\Controllers\Controller;
use App\Model\Company;
use App\Model\Comcheck;
use App\Model\Comcheckdetail;
use Illuminate\Http\Request;
use App\Http\Requests\CheckRequest;
use Illuminate\Support\Facades\Storage;
use Carbon\Carbon;
use Maatwebsite\Excel\Excel;
use Illuminate\Support\Facades\DB;
use PDO;

class StatisticsController extends Controller
{
    /**
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     * 区域检查统计查询
     */
    public function Statistics(){
        return view('check/statistics');
    }
    /**
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     * 今日检查统计
     */
    public function TodayStatistics(){
        $list = self::GetTodayList();
        $count = $list->count();
        $comcheckId = [];
        $companyId = [];
        $userId = [];
        foreach ($list as $k=>$v){
            in_array($v['companyId'],$companyId)?'':$companyId[] = $v['companyId'];
            $userId[] = $v['userId'];
            $comcheckId[] = $v['id'];
        }
        $count = count($companyId);
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$comcheckId)->where('status','=',2)->get();        //初查隐患详情
        $comcheckagain = Comcheckagain::whereIn('checkId',$comcheckId)->get();        //复查隐患详情
        $comcheckagainId = [];
        foreach ($comcheckagain as $k=>$v){
            $comcheckagainId[] =$v['id'];
        }
        $comcheckagaindetail = Comcheckagaindetail::whereIn('checkId',$comcheckagainId)->where('status','=',3)->get();
        /**获取省市地区街道信息*/
        $company = Company::whereIn('id',$companyId)->get();
        $areaid = [];
        foreach ($company as $k=>$v){
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
        }
        $area = Area::whereIn('id',$areaid)->get();
        /**获取省市地区街道信息*/
        return view('check/today_statistics',[
            'list'=>$list,
            'count'=>$count,
            'comcheckdetail'=>$comcheckdetail,
            'comcheckagain'=>$comcheckagain,
            'comcheckagaindetail'=>$comcheckagaindetail,
            'area'=>$area,
        ]);

    }
    /**
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     * 区域检查统计
     */
    public function AreaStatistics(Request $request){
        $province  = Area::whereNull('pid')->get(['id','name']);

        if (!empty($_POST)){

            $data = $request->input();
            if(empty($data['start_date'])) {
                $data['start_date'] = date('Y-m-d H:i:s',strtotime("-1 year"));
            }
            if(empty($data['end_date'])) {
                $data['end_date'] = date('Y-m-d H:i:s',time());
            }
            $start_date = $data['start_date'];
            $end_date = $data['end_date'];
            $data['end_date'] = $data['end_date'] = date('Y-m-d H:i:s',strtotime('+1 day',strtotime($end_date)));
            $province1 = $data['province'];
            $city = $data['city'];
            $town = $data['town'];
            $street = $data['street'];
            /**搜索筛选初查*/
            $query = Comcheck::leftJoin('company','comcheck.companyId','company.id')->select('comcheck.*','company.city','company.district','company.street');
            $data['start_date'] && $query->where('comcheck.created', '>=', $data['start_date']);
            $data['end_date'] && $query->where('comcheck.created', '<=', $data['end_date']);
            $data['province'] && $query->where('company.province', '=', $data['province']);
            $data['city'] && $query->where('company.city', '=', $data['city']);
            $data['town'] && $query->where('company.district', '=', $data['town']);
            $data['street'] && $query->where('company.street', '=', $data['street']);
            $count = $query->count();
            $list = $query->orderBy('comcheck.id','desc')->get();
            $result = json_decode(json_encode($list), true);
            /**搜索筛选初查*/
            /**搜索筛选初查*/
            $data['start_date'] = strtotime($data['start_date']);
            $data['end_date'] = strtotime($data['end_date']);
            $queryagin = Comcheckagain::leftJoin('company','comcheckagain.companyId','company.id')->select('comcheckagain.*','company.city','company.district','company.street');
            $data['start_date'] && $queryagin->where('comcheckagain.checkTime', '>=', $data['start_date']);
            $data['end_date'] && $queryagin->where('comcheckagain.checkTime', '<=', $data['end_date']);
            $data['province'] && $queryagin->where('company.province', '=', $data['province']);
            $data['city'] && $queryagin->where('company.city', '=', $data['city']);
            $data['town'] && $queryagin->where('company.district', '=', $data['town']);
            $data['street'] && $queryagin->where('company.street', '=', $data['street']);
            $countagain = $queryagin->count();
            $listagain = $queryagin->orderBy('comcheckagain.id','desc')->get();
            $resultagain = json_decode(json_encode($listagain), true);
            /**搜索筛选初查*/
            /**根据省市区街道重新排序初查*/
            $newresult = [];
            $comcheckid = [];
            foreach ($result as $v){
                $comcheckid[] = $v['id'];
                if (array_key_exists($v['city'],$newresult)){
                    if (array_key_exists($v['district'],$newresult[$v['city']])) {
                        if (array_key_exists($v['street'],$newresult[$v['city']][$v['district']])) {
                            array_push($newresult[$v['city']][$v['district']][$v['street']]['comcheck'],$v);
                        }else{
                            $newresult[$v['city']][$v['district']][$v['street']]['comcheck'][] = $v;
                        }
                    }else{
                        $newresult[$v['city']][$v['district']][$v['street']]['comcheck'][] = $v;
                    }
                }else{
                    $newresult[$v['city']][$v['district']][$v['street']]['comcheck'][] = $v;
                }
            }
            /**根据省市区街道重新排序初查*/
            /**根据省市区街道重新排序复查**/
            $comcheckagainid = [];
            foreach ($resultagain as $v){
                $comcheckagainid[] = $v['id'];

                if (array_key_exists($v['city'],$newresult)){
                    if (array_key_exists($v['district'],$newresult[$v['city']])) {
                        if (array_key_exists($v['street'],$newresult[$v['city']][$v['district']][$v['street']])) {
                            array_push($newresult[$v['city']][$v['district']][$v['street']]['again'],$v);
                        }else{
                            $newresult[$v['city']][$v['district']][$v['street']]['again'][] = $v;
                        }
                    }else{
                        $newresult[$v['city']][$v['district']][$v['street']]['again'][] = $v;
                    }
                }else{
                    $newresult[$v['city']][$v['district']][$v['street']]['again'][] = $v;
                }
            }
            /**根据省市区街道重新排序复查**/
            /**获取初查详情*/
            $comcheckdetailtree =  Comcheckdetail::leftJoin('checkentry','comcheckdetail.entryId','checkentry.id')
                ->leftJoin('checkgroup','checkentry.groupId','checkgroup.id')
                ->select('comcheckdetail.*','checkentry.entry','checkgroup.status as groupstatus')
                ->whereIn('comcheckdetail.checkId',$comcheckid)->where('comcheckdetail.status','=',2)
                ->get();
            $comcheckdetailtree = json_decode(json_encode($comcheckdetailtree), true);
            /**获取初查详情end*/
            /**获取复查详情*/
            $comcheckagaindetailtree = Comcheckagaindetail::whereIn('checkId',$comcheckagainid)->get();
            $comcheckagaindetailtree = json_decode(json_encode($comcheckagaindetailtree), true);
            /**获取复查详情end*/
            /**获取省市地区**/
            $provinceid = [];
            foreach ($newresult as $k=>$v){
                $provinceid[] = $k;
                foreach ($v as $k1=>$v1){
                    $provinceid[] = $k1;
                    foreach ($v1 as $k2=>$v2){
                        $provinceid[] = $k2;
                    }
                }
            }
            $province = Area::whereIn('id',$provinceid)->get();
            $provinceresult = [];
            $province = json_decode(json_encode($province));
            foreach ($province as $k=>$v){
                $provinceresult[$v->id]=$v;
            }
            /**获取省市地区**/
            $comcheckId = [];
            foreach ($list as $k=>$v){
                $comcheckId[] = $v['id'];
            }
            $comcheckdetail = Comcheckdetail::whereIn('checkId',$comcheckId)->where('status','=',2)->get();
            $comcheckagain = Comcheckagain::whereIn('checkId',$comcheckId)->get();
            $comcheckagainId = [];
            foreach ($comcheckagain as $k=>$v){
                $comcheckagainId[] =$v['id'];
            }
            $comcheckagaindetail = Comcheckagaindetail::whereIn('checkId',$comcheckagainId)->where('status','=',3)->get();
            return view('check/result_list',[
                'list'=>$list,
                'start_date'=>$start_date,
                'end_date'=>$end_date,
                'province'=>$province1,
                'city'=>$city,
                'town'=>$town,
                'street'=>$street,
                'tree'=>$list,
                'count'=>$count,
                'result'=>$newresult,
                'provinceresult'=>$provinceresult,
                'comcheckdetail'=>$comcheckdetail,
                'comcheckagain'=>$comcheckagain,
                'comcheckagaindetail'=>$comcheckagaindetail,
                'comcheckdetailtree'=>$comcheckdetailtree,
                'comcheckagaindetailtree'=>$comcheckagaindetailtree,
                'countagain'=>$countagain,
            ]);
        }
        return view('check/area_statistics',[
            'province'=>$province
        ]);
    }
    /**
     * 今日检查导出
     */
    public function export()
    {
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH');
        $objActSheet->setCellValue("A1","检查情况统计表");
        $objActSheet->mergeCells('A1:AH1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","序号");
        $objActSheet->setCellValue("B2","企业名称");
        $objActSheet->setCellValue("C2","行业类别");
        $objActSheet->setCellValue("D2","企业地址");
        $objActSheet->setCellValue("E2","员工人数");
        $objActSheet->setCellValue("F2","占地面积");
        $objActSheet->setCellValue("G2","固定资产");
        $objActSheet->setCellValue("H2","年销售收入");
        $objActSheet->setCellValue("I2","法定代表人");
        $objActSheet->setCellValue("J2","联系电话");
        $objActSheet->setCellValue("K2","联系人");
        $objActSheet->setCellValue("L2","联系电话");
        $objActSheet->getStyle('B2:L2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('B2:L2')->getFill()->getStartColor()->setARGB('FFFF0000');
        $line1 =  ['B','C','D','E','F','G','H','I','J','K','L','M','N'];
        foreach ($line1 as $l) {
            $objActSheet->mergeCells($l . '2:' . $l . '3');
        }
        //part2
        $objActSheet->setCellValue("M2","检查日期");
        $objActSheet->setCellValue("N2","检查人员");
        $objActSheet->getStyle('M2:N2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('M2:N2')->getFill()->getStartColor()->setARGB('FFFF8000');//黄褐
        //part3
        $objActSheet->setCellValue("O2","消防安全类");
        $objActSheet->setCellValue("O3","基础档案(条)");
        $objActSheet->setCellValue("P3","现场(条)");
        $objActSheet->setCellValue("S3","隐患总数(条)");
        $objActSheet->mergeCells("O2:S2");
        $objActSheet->getStyle('O2:S3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('O2:S3')->getFill()->getStartColor()->setARGB('FFFFFF00');//黄色
        //part4
        $objActSheet->setCellValue("T2","生产安全类");
        $objActSheet->setCellValue("T3","基础档案(条)");
        $objActSheet->setCellValue("U3","现场(条)");
        $objActSheet->setCellValue("X3","隐患总数(条)");
        $objActSheet->mergeCells("T2:X2");
        $objActSheet->getStyle('T2:X3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('T2:X3')->getFill()->getStartColor()->setARGB('FF80E000');//墨绿
        //part5
        $objActSheet->setCellValue("Y2","复查日期");
        $objActSheet->setCellValue("Z2","复查人员");
        $objActSheet->mergeCells("Y2:Y3");
        $objActSheet->mergeCells("Z2:Z3");
        $objActSheet->getStyle('Y2:Z2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('Y2:Z2')->getFill()->getStartColor()->setARGB('FFECECEC');//银白
        //part6
        $objActSheet->setCellValue("AA2","消防安全类");
        $objActSheet->setCellValue("AA3","已整改(条)");
        $objActSheet->setCellValue("AB3","部分整改(条)");
        $objActSheet->setCellValue("AC3","未整改(条)");
        $objActSheet->setCellValue("AD3","整改率(%)");
        $objActSheet->mergeCells("AA2:AD2");
        $objActSheet->getStyle('AA2:AD3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('AA2:AD3')->getFill()->getStartColor()->setARGB('FF0088FF');//天蓝
        //part7
        $objActSheet->setCellValue("AE2","生产安全类");
        $objActSheet->setCellValue("AE3","已整改(条)");
        $objActSheet->setCellValue("AF3","部分整改(条)");
        $objActSheet->setCellValue("AG3","未整改(条)");
        $objActSheet->setCellValue("AH3","整改率(%)");
        $objActSheet->mergeCells("AE2:AH2");
        $objActSheet->getStyle('AE2:AH3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('AE2:AH3')->getFill()->getStartColor()->setARGB('FF9000FF');//紫色
        //统一设置左右居中、字号、宽度
        foreach ($letter as $l){
            $objActSheet->getStyle($l.'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
            $objActSheet->getStyle($l.'3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
            $objActSheet->getStyle($l.'2')->getFont()->setSize(13);
            $objActSheet->getStyle($l.'3')->getFont()->setSize(13);
            $objActSheet->getColumnDimension($l)->setWidth(19);
        }
        //单独设置宽度
        $objActSheet->getColumnDimension('A')->setWidth(7);
        $objActSheet->getColumnDimension('B')->setWidth(40);
        $objActSheet->getColumnDimension('D')->setWidth(40);

        /**填充内容*/
        $orirow = 4;
        $i = 1;

        $list = self::GetTodayList();
        $comcheckId = [];
        $companyId = [];
        foreach ($list as $k=>$v){
            $companyId[] = $v['companyId'];
            $comcheckId[] = $v['id'];
        }
        $company = Company::whereIn('id',$companyId)->get();
        $areaid = [];
        $categoryId = [];
        foreach ($company as $k=>$v){
            array_push($categoryId,$v->category);
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
        }
        $category = Checkcategory::whereIn('id',$categoryId)->get();
        $area = Area::whereIn('id',$areaid)->get();
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$comcheckId)->where('status','=',2)->get();
        $comcheckagain = Comcheckagain::whereIn('checkId',$comcheckId)->get();
        $comcheckagainId = [];
        foreach ($comcheckagain as $k=>$v){
            $comcheckagainId[] =$v['id'];
        }
        $comcheckagaindetail = Comcheckagaindetail::leftJoin('checkentry','comcheckagaindetail.entryId','checkentry.id')
            ->leftJoin('checkgroup','checkentry.groupId','checkgroup.id')
            ->select('comcheckagaindetail.*','checkentry.entry','checkgroup.status as groupstatus','checkgroup.type as grouptype')
            ->whereIn('comcheckagaindetail.checkId',$comcheckagainId)->get();
        /*获取初查详情*/
        $comcheckid = [];

        foreach ($list as $v){
            $comcheckid[] = $v['id'];
        }
        $comcheckdetailtree =  Comcheckdetail::leftJoin('checkentry','comcheckdetail.entryId','checkentry.id')
            ->leftJoin('checkgroup','checkentry.groupId','checkgroup.id')
            ->select('comcheckdetail.*','checkentry.entry','checkgroup.status as groupstatus','checkgroup.type as grouptype')
            ->whereIn('comcheckdetail.checkId',$comcheckid)->where('comcheckdetail.status','=',2)
            ->get();
        $comcheckdetailtree = json_decode(json_encode($comcheckdetailtree), true);
        /*获取初查详情*/

        foreach ($list as $u) {
            $adress = '';
            $ren = 0;
            $fren = 0;
            $wu = 0;
            $fwu = 0;
            $huan = 0;
            $fhuan = 0;
            $guan = 0;
            $fguan = 0;
            $fucha = 0;
            $yzhenggai = 0;
            $bfzhenggai = 0;
            $wzhenggai = 0;
            $fyzhenggai = 0;
            $fbfzhenggai = 0;
            $fwzhenggai = 0;
            $fuchadate = '';
            $fuchauser = '';
            foreach ($area as $a) {
                if ($u->company->city == $a->id){
                    $adress .=$a->name;
                }
                if ($u->company->district == $a->id) {
                    $adress .=$a->name;
                }
                if ($u->company->street == $a->id) {
                    $adress .=$a->name;

                }
            }
            $objActSheet->setCellValue("A".$orirow,$i);
            $objActSheet->setCellValue("B".$orirow,$u->company->comName);
            foreach ($category as $k=>$v){
                if ($u->company->category == $v->id){
                    $objActSheet->setCellValue("C".$orirow,$v->name);
                }
            }
            foreach ($comcheckdetailtree as $k1=>$u1){
                if ($u1['grouptype'] == 1){
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 1){
                        $ren += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 2){
                        $wu += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 3){
                        $huan += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 4){
                        $guan += 1;
                    }
                }
                if ($u1['grouptype'] == 2){
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 1){
                        $fren += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 2){
                        $fwu += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 3){
                        $fhuan += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 4){
                        $fguan += 1;
                    }
                }

            }

            foreach ($comcheckagain as $s=>$v) {
                $a = User::find($v->userId)->name;
                    if (($v->checkId == $u->id)){
                        $fuchauser = User::find($v->userId)->name;
                        $fuchadate = date('Y.m.d',$v->checkTime);
                    }

                foreach ($comcheckagaindetail as $v1) {
                    if ($v->checkId == $u->id && $v->id == $v1->checkId) {
                        if ($v1['grouptype'] == 1){
                            if ($v1['status'] == 1){
                                $wzhenggai ++;
                            }
                            if ($v1['status'] == 2){
                                $bfzhenggai ++;
                            }
                            if ($v1['status'] == 3){
                                $yzhenggai ++;
                            }
                        }
                        if ($v1['grouptype'] == 2){
                            if ($v1['status'] == 1){
                                $fwzhenggai ++;
                            }
                            if ($v1['status'] == 2){
                                $fbfzhenggai ++;
                            }
                            if ($v1['status'] == 3){
                                $fyzhenggai ++;
                            }
                        }
                    }
                }
            }
            $objActSheet->setCellValue("D".$orirow,$adress.$u->company->address);
            $objActSheet->setCellValue("E".$orirow,$u->company->workersNum);
            $objActSheet->setCellValue("F".$orirow,$u->company->space);
            $objActSheet->setCellValue("G".$orirow,$u->company->assets);
            $objActSheet->setCellValue("H".$orirow,$u->company->income);
            $objActSheet->setCellValue("I".$orirow,$u->company->leader);
            $objActSheet->setCellValue("J".$orirow,$u->company->leaderPhone);
            $objActSheet->setCellValue("K".$orirow,$u->company->leader);
            $objActSheet->setCellValue("L".$orirow,$u->company->leaderPhone);
            $objActSheet->setCellValue("M".$orirow,$u->created);
            $objActSheet->setCellValue("N".$orirow,$u->users->name);
            $objActSheet->setCellValue("O".$orirow,$ren);
            $objActSheet->setCellValue("P".$orirow,$wu);
            $objActSheet->setCellValue("S".$orirow,$ren+$wu+$huan+$guan);
            $objActSheet->setCellValue("T".$orirow,$fren);
            $objActSheet->setCellValue("U".$orirow,$fwu);
            $objActSheet->setCellValue("X".$orirow,$fren+$fwu+$fhuan+$fguan);
            $objActSheet->setCellValue("Y".$orirow,$fuchadate);
            $objActSheet->setCellValue("Z".$orirow,$fuchauser);
            $objActSheet->setCellValue("AA".$orirow,$yzhenggai);
            $objActSheet->setCellValue("AB".$orirow,$bfzhenggai);
            $objActSheet->setCellValue("AC".$orirow,$wzhenggai);
            if (!empty($yzhenggai+$wzhenggai+$bfzhenggai)){
                $objActSheet->setCellValue("AD".$orirow,sprintf("%.2f",$yzhenggai/($yzhenggai+$wzhenggai+$bfzhenggai)*100).'%');
            }
            $objActSheet->setCellValue("AE".$orirow,$fyzhenggai);
            $objActSheet->setCellValue("AF".$orirow,$fbfzhenggai);
            $objActSheet->setCellValue("AG".$orirow,$fwzhenggai);
            if (!empty($fyzhenggai + $fwzhenggai + $fbfzhenggai)) {
                $objActSheet->setCellValue("AH" . $orirow, sprintf("%.2f", $yzhenggai / ($fyzhenggai + $fwzhenggai + $fbfzhenggai) * 100) . '%');
            }
            $i ++;
            $orirow ++;

        }
        /**填充内容*/
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 4;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(30);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getStyle('A1:AH'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(40);//单独设置行高度
        $objActSheet->getRowDimension(2)->setRowHeight(40);//单独设置行高度
        $date = Carbon::now()->toDateString();//表名前缀日期
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$date.''."检查统计".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');

        exit();
        $cellData = ['序号','隐患统计','排查地点','排查人'];
        $date = Carbon::now()->toDateString();

        \Maatwebsite\Excel\Facades\Excel::create($date.'检查统计',function($excel) use ($cellData){
            $excel->sheet('score', function($sheet) use ($cellData){
                $list = self::GetTodayList();
                $comcheckId = [];
                $companyId = [];
                foreach ($list as $k=>$v){
                    $companyId[] = $v['companyId'];
                    $comcheckId[] = $v['id'];
                }
                $company = Company::whereIn('id',$companyId)->get();
                $areaid = [];
                foreach ($company as $k=>$v){
                    (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
                    (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
                    (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
                    (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
                }
                $area = Area::whereIn('id',$areaid)->get();
                $comcheckdetail = Comcheckdetail::whereIn('checkId',$comcheckId)->where('status','=',2)->get();
                $comcheckagain = Comcheckagain::whereIn('checkId',$comcheckId)->get();
                $comcheckagainId = [];
                foreach ($comcheckagain as $k=>$v){
                    $comcheckagainId[] =$v['id'];
                }
                $comcheckagaindetail = Comcheckagaindetail::whereIn('checkId',$comcheckagainId)->where('status','=',3)->get();                $company = Company::whereIn('id',$companyId)->get();
                $sheet->setWidth(array(
                    'A'     =>  5,
                    'B'     =>  42,
                    'C'     =>  30,
                    'D'     =>  12
                ))->rows($cellData)->setFontSize(12);
                $sheet->cells('A1:D1', function($cells) {
                    $cells->setBackground('#93b7f3');
                    $cells->setFontWeight('bold');
                    $cells->setFontSize(14);
                });
                $sheet->cell('A2',$cellData[0]);
                $sheet->cell('B1',$cellData[1]);
                $sheet->cell('C1',$cellData[2]);
                $sheet->cell('D1',$cellData[3]);
                $orirow = 2;
                $i = 1;
                foreach ($list as $u){
                    $adress = '';
                    $chucha = 0;$fucha = 0;
                    foreach ($comcheckdetail as $v){
                        if ($v->checkId == $u->id){
                            $chucha ++;
                        }
                    }
                    foreach ($comcheckagain as $v) {
                        foreach ($comcheckagaindetail as $v1) {
                            if ($v->checkId == $u->id && $v->id == $v1->checkId) {
                                $fucha++;
                            }
                        }
                    }
                    foreach ($area as $a) {
                        if ($u->company->city == $a->id){
                            $adress .=$a->name;
                        }
                        if ($u->company->district == $a->id) {
                            $adress .=$a->name;
                        }
                        if ($u->company->street == $a->id) {
                            $adress .=$a->name;

                        }
                    }
                    $sheet->cell('A'.$orirow,$i);
                    $sheet->cell('B'.$orirow,$u->company->comName.'初查时共有'.$chucha.'条隐患，复查时已整改'.$fucha.'条');
                    $sheet->cell('C'.$orirow,$adress.$u->company->address);
                    $sheet->cell('D'.$orirow,$u->users->name);
                    $orirow ++;
                    $i++;
                }
                $sheet->rows($cellData);
            });
        })->export('xls');
    }

    /**
     * @param Request $request
     * @param $start_date
     * @param $end_date
     * 区域检查导出
     */
    public function exportlist(Request $request)
    {
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH');
        $objActSheet->setCellValue("A1","检查情况统计表");
        $objActSheet->mergeCells('A1:AH1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","序号");
        $objActSheet->setCellValue("B2","企业名称");
        $objActSheet->setCellValue("C2","行业类别");
        $objActSheet->setCellValue("D2","企业地址");
        $objActSheet->setCellValue("E2","员工人数");
        $objActSheet->setCellValue("F2","占地面积");
        $objActSheet->setCellValue("G2","固定资产");
        $objActSheet->setCellValue("H2","年销售收入");
        $objActSheet->setCellValue("I2","法定代表人");
        $objActSheet->setCellValue("J2","联系电话");
        $objActSheet->setCellValue("K2","联系人");
        $objActSheet->setCellValue("L2","联系电话");
        $objActSheet->getStyle('B2:L2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('B2:L2')->getFill()->getStartColor()->setARGB('FFFF0000');
        $line1 =  ['B','C','D','E','F','G','H','I','J','K','L','M','N'];
        foreach ($line1 as $l) {
            $objActSheet->mergeCells($l . '2:' . $l . '3');
        }
        //part2
        $objActSheet->setCellValue("M2","检查日期");
        $objActSheet->setCellValue("N2","检查人员");
        $objActSheet->getStyle('M2:N2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('M2:N2')->getFill()->getStartColor()->setARGB('FFFF8000');//黄褐
        //part3
        $objActSheet->setCellValue("O2","消防安全类");
        $objActSheet->setCellValue("O3","基础档案(条)");
        $objActSheet->setCellValue("P3","现场(条)");
        $objActSheet->setCellValue("S3","隐患总数(条)");
        $objActSheet->mergeCells("O2:S2");
        $objActSheet->getStyle('O2:S3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('O2:S3')->getFill()->getStartColor()->setARGB('FFFFFF00');//黄色
        //part4
        $objActSheet->setCellValue("T2","生产安全类");
        $objActSheet->setCellValue("T3","基础档案(条)");
        $objActSheet->setCellValue("U3","现场(条)");
        $objActSheet->setCellValue("X3","隐患总数(条)");
        $objActSheet->mergeCells("T2:X2");
        $objActSheet->getStyle('T2:X3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('T2:X3')->getFill()->getStartColor()->setARGB('FF80E000');//墨绿
        //part5
        $objActSheet->setCellValue("Y2","复查日期");
        $objActSheet->setCellValue("Z2","复查人员");
        $objActSheet->mergeCells("Y2:Y3");
        $objActSheet->mergeCells("Z2:Z3");
        $objActSheet->getStyle('Y2:Z2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('Y2:Z2')->getFill()->getStartColor()->setARGB('FFECECEC');//银白
        //part6
        $objActSheet->setCellValue("AA2","消防安全类");
        $objActSheet->setCellValue("AA3","已整改(条)");
        $objActSheet->setCellValue("AB3","部分整改(条)");
        $objActSheet->setCellValue("AC3","未整改(条)");
        $objActSheet->setCellValue("AD3","整改率(%)");
        $objActSheet->mergeCells("AA2:AD2");
        $objActSheet->getStyle('AA2:AD3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('AA2:AD3')->getFill()->getStartColor()->setARGB('FF0088FF');//天蓝
        //part7
        $objActSheet->setCellValue("AE2","生产安全类");
        $objActSheet->setCellValue("AE3","已整改(条)");
        $objActSheet->setCellValue("AF3","部分整改(条)");
        $objActSheet->setCellValue("AG3","未整改(条)");
        $objActSheet->setCellValue("AH3","整改率(%)");
        $objActSheet->mergeCells("AE2:AH2");
        $objActSheet->getStyle('AE2:AH3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $objActSheet->getStyle('AE2:AH3')->getFill()->getStartColor()->setARGB('FF9000FF');//紫色
        //统一设置左右居中、字号、宽度
        foreach ($letter as $l){
            $objActSheet->getStyle($l.'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
            $objActSheet->getStyle($l.'3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
            $objActSheet->getStyle($l.'2')->getFont()->setSize(13);
            $objActSheet->getStyle($l.'3')->getFont()->setSize(13);
            $objActSheet->getColumnDimension($l)->setWidth(19);
        }
        //单独设置宽度
        $objActSheet->getColumnDimension('A')->setWidth(7);
        $objActSheet->getColumnDimension('B')->setWidth(40);
        $objActSheet->getColumnDimension('D')->setWidth(40);

        /**填充内容*/
        $orirow = 4;
        $i = 1;
        $data = [];
        $data['start_date'] = $request->input('start_date');
        $data['end_date'] = $request->input('end_date');
        $data['province'] = $request->input('province');
        $data['city'] = $request->input('city');
        $data['town'] = $request->input('town');
        $data['street'] = $request->input('street');
        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $data['end_date'] = $data['end_date'] = date('Y-m-d H:i:s',strtotime('+1 day',strtotime($end_date)));
        $query = Comcheck::leftJoin('company','comcheck.companyId','company.id')->select('comcheck.*','company.city','company.district','company.street');
        $data['start_date'] && $query->where('comcheck.created', '>=', $data['start_date']);
        $data['end_date'] && $query->where('comcheck.created', '<=', $data['end_date']);
        $data['province'] && $query->where('company.province', '=', $data['province']);
        $data['city'] && $query->where('company.city', '=', $data['city']);
        $data['town'] && $query->where('company.district', '=', $data['town']);
        $data['street'] && $query->where('company.street', '=', $data['street']);
        $list = $query->orderBy('comcheck.id','desc')->get();

        $comcheckId = [];
        $companyId = [];
        foreach ($list as $k=>$v){
            $companyId[] = $v['companyId'];
            $comcheckId[] = $v['id'];
        }
        $company = Company::whereIn('id',$companyId)->get();
        $areaid = [];
        $categoryId = [];
        foreach ($company as $k=>$v){
            array_push($categoryId,$v->category);
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
        }
        $category = Checkcategory::whereIn('id',$categoryId)->get();
        $area = Area::whereIn('id',$areaid)->get();
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$comcheckId)->where('status','=',2)->get();
        $comcheckagain = Comcheckagain::whereIn('checkId',$comcheckId)->get();
        $comcheckagainId = [];
        foreach ($comcheckagain as $k=>$v){
            $comcheckagainId[] =$v['id'];
        }
        $comcheckagaindetail = Comcheckagaindetail::leftJoin('checkentry','comcheckagaindetail.entryId','checkentry.id')
            ->leftJoin('checkgroup','checkentry.groupId','checkgroup.id')
            ->select('comcheckagaindetail.*','checkentry.entry','checkgroup.status as groupstatus','checkgroup.type as grouptype')
            ->whereIn('comcheckagaindetail.checkId',$comcheckagainId)->get();
        /*获取初查详情*/
        $comcheckid = [];

        foreach ($list as $v){
            $comcheckid[] = $v['id'];
        }
        $comcheckdetailtree =  Comcheckdetail::leftJoin('checkentry','comcheckdetail.entryId','checkentry.id')
            ->leftJoin('checkgroup','checkentry.groupId','checkgroup.id')
            ->select('comcheckdetail.*','checkentry.entry','checkgroup.status as groupstatus','checkgroup.type as grouptype')
            ->whereIn('comcheckdetail.checkId',$comcheckid)->where('comcheckdetail.status','=',2)
            ->get();
        $comcheckdetailtree = json_decode(json_encode($comcheckdetailtree), true);
        /*获取初查详情*/

        foreach ($list as $u) {
            $adress = '';
            $ren = 0;
            $fren = 0;
            $wu = 0;
            $fwu = 0;
            $huan = 0;
            $fhuan = 0;
            $guan = 0;
            $fguan = 0;
            $fucha = 0;
            $yzhenggai = 0;
            $bfzhenggai = 0;
            $wzhenggai = 0;
            $fyzhenggai = 0;
            $fbfzhenggai = 0;
            $fwzhenggai = 0;
            $fuchadate = '';
            $fuchauser = '';

            foreach ($area as $a) {
                if ($u->company->city == $a->id){
                    $adress .=$a->name;
                }
                if ($u->company->district == $a->id) {
                    $adress .=$a->name;
                }
                if ($u->company->street == $a->id) {
                    $adress .=$a->name;

                }
            }
            $objActSheet->setCellValue("A".$orirow,$i);
            $objActSheet->setCellValue("B".$orirow,$u->company->comName);
            foreach ($category as $k=>$v){
                if ($u->company->category == $v->id){
                    $objActSheet->setCellValue("C".$orirow,$v->name);
                }
            }
            foreach ($comcheckdetailtree as $k1=>$u1){
                if ($u1['grouptype'] == 1){
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 1){
                        $ren += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 2){
                        $wu += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 3){
                        $huan += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 4){
                        $guan += 1;
                    }
                }
                if ($u1['grouptype'] == 2){
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 1){
                        $fren += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 2){
                        $fwu += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 3){
                        $fhuan += 1;
                    }
                    if ($u1['checkId'] == $u['id'] && $u1['groupstatus'] == 4){
                        $fguan += 1;
                    }
                }

            }

            foreach ($comcheckagain as $s=>$v) {
                if ($v->checkId == $u->id) {
                    $fuchauser = User::find($v->userId)->name;
                    $fuchadate = date('Y.m.d', $v->checkTime);
                }

                foreach ($comcheckagaindetail as $v1) {
                    if ($v->checkId == $u->id && $v->id == $v1->checkId) {
                        if ($v1['grouptype'] == 1){
                            if ($v1['status'] == 1){
                                $wzhenggai ++;
                            }
                            if ($v1['status'] == 2){
                                $bfzhenggai ++;
                            }
                            if ($v1['status'] == 3){
                                $yzhenggai ++;
                            }
                        }
                        if ($v1['grouptype'] == 2){
                            if ($v1['status'] == 1){
                                $fwzhenggai ++;
                            }
                            if ($v1['status'] == 2){
                                $fbfzhenggai ++;
                            }
                            if ($v1['status'] == 3){
                                $fyzhenggai ++;
                            }
                        }
                    }
                }
            }
            $objActSheet->setCellValue("D".$orirow,$adress.$u->company->address);
            $objActSheet->setCellValue("E".$orirow,$u->company->workersNum);
            $objActSheet->setCellValue("F".$orirow,$u->company->space);
            $objActSheet->setCellValue("G".$orirow,$u->company->assets);
            $objActSheet->setCellValue("H".$orirow,$u->company->income);
            $objActSheet->setCellValue("I".$orirow,$u->company->leader);
            $objActSheet->setCellValue("J".$orirow,$u->company->leaderPhone);
            $objActSheet->setCellValue("K".$orirow,$u->company->leader);
            $objActSheet->setCellValue("L".$orirow,$u->company->leaderPhone);
            $objActSheet->setCellValue("M".$orirow,$u->created);
            $objActSheet->setCellValue("N".$orirow,$u->users->name);
            $objActSheet->setCellValue("O".$orirow,$ren);
            $objActSheet->setCellValue("P".$orirow,$wu);
            $objActSheet->setCellValue("S".$orirow,$ren+$wu+$huan+$guan);
            $objActSheet->setCellValue("T".$orirow,$fren);
            $objActSheet->setCellValue("U".$orirow,$fwu);
            $objActSheet->setCellValue("X".$orirow,$fren+$fwu+$fhuan+$fguan);
            $objActSheet->setCellValue("Y".$orirow,$fuchadate);
            $objActSheet->setCellValue("Z".$orirow,$fuchauser);
            $objActSheet->setCellValue("AA".$orirow,$yzhenggai);
            $objActSheet->setCellValue("AB".$orirow,$bfzhenggai);
            $objActSheet->setCellValue("AC".$orirow,$wzhenggai);
            if (!empty($yzhenggai+$wzhenggai+$bfzhenggai)){
                $objActSheet->setCellValue("AD".$orirow,sprintf("%.2f",$yzhenggai/($yzhenggai+$wzhenggai+$bfzhenggai)*100).'%');
            }
            $objActSheet->setCellValue("AE".$orirow,$fyzhenggai);
            $objActSheet->setCellValue("AF".$orirow,$fbfzhenggai);
            $objActSheet->setCellValue("AG".$orirow,$fwzhenggai);
            if (!empty($fyzhenggai + $fwzhenggai + $fbfzhenggai)) {
                $objActSheet->setCellValue("AH" . $orirow, sprintf("%.2f", $yzhenggai / ($fyzhenggai + $fwzhenggai + $fbfzhenggai) * 100) . '%');
            }
            $i ++;
            $orirow ++;

        }
        /**填充内容*/
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 4;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(30);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getStyle('A1:AH'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(40);//单独设置行高度
        $objActSheet->getRowDimension(2)->setRowHeight(40);//单独设置行高度
        $date = Carbon::now()->toDateString();//表名前缀日期
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$start_date.'至'.$end_date."检查统计".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');

        exit();
        $date = Carbon::now()->toDateString();
        $data = [];
        $data['start_date'] = $start_date;
        $data['end_date'] = $end_date;
        \Maatwebsite\Excel\Facades\Excel::create($date.'检查统计',function($excel) use ($data){
            $excel->sheet('score', function($sheet) use ($data){
                $query = Comcheck::with('company','users','comcheckdetail');
                $data['start_date'] && $query->where('created', '>=', $data['start_date']);
                $data['end_date'] && $query->where('created', '<=', $data['end_date']);
                $list = $query->orderBy('id','desc')->get();
                $comcheckId = [];
                $companyId = [];
                foreach ($list as $k=>$v){
                    $companyId[] = $v['companyId'];
                    $comcheckId[] = $v['id'];
                }
                $company = Company::whereIn('id',$companyId)->get();
                $areaid = [];
                foreach ($company as $k=>$v){
                    (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
                    (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
                    (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
                    (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
                }
                $area = Area::whereIn('id',$areaid)->get();
                $comcheckdetail = Comcheckdetail::whereIn('checkId',$comcheckId)->where('status','=',2)->get();
                $comcheckagain = Comcheckagain::whereIn('checkId',$comcheckId)->get();
                $comcheckagainId = [];
                foreach ($comcheckagain as $k=>$v){
                    $comcheckagainId[] =$v['id'];
                }
                $comcheckagaindetail = Comcheckagaindetail::whereIn('checkId',$comcheckagainId)->where('status','=',3)->get();                  $company = Company::whereIn('id',$companyId)->get();
                $sheet->setWidth(array(
                    'A'     =>  5,
                    'B'     =>  42,
                    'C'     =>  30,
                    'D'     =>  12
                ))->rows($list)->setFontSize(12);
                $sheet->cells('A1:D1', function($cells) {
                    $cells->setBackground('#93b7f3');
                    $cells->setFontWeight('bold');
                    $cells->setFontSize(14);
                });
                $sheet->cell('A1','序号');
                $sheet->cell('B1','隐患统计');
                $sheet->cell('C1','排查地点');
                $sheet->cell('D1','排查人');
                $orirow = 2;
                $i = 1;
                foreach ($list as $u){
                    $chucha = 0;$fucha = 0;$adress = '';
                    foreach ($comcheckdetail as $v){
                        if ($v->checkId == $u->id){
                            $chucha ++;
                        }
                    }
                    foreach ($comcheckagain as $v){
                        foreach ($comcheckagaindetail as $v1) {
                            if ($v->checkId == $u->id && $v->id == $v1->checkId) {
                                $fucha++;
                            }
                        }
                    }
                    foreach ($area as $a) {
                        if ($u->company->city == $a->id){
                            $adress .=$a->name;
                        }
                        if ($u->company->district == $a->id) {
                            $adress .=$a->name;
                        }
                        if ($u->company->street == $a->id) {
                            $adress .=$a->name;
                        }
                    }
                    $sheet->cell('A'.$orirow,$i);
                    $sheet->cell('B'.$orirow,$u->company->comName.'初查时共有'.$chucha.'条隐患，复查时已整改'.$fucha.'条');
                    $sheet->cell('C'.$orirow,$adress.$u->company->address);
                    $sheet->cell('D'.$orirow,$u->users->name);
                    $orirow ++;
                    $i++;
                }
                $sheet->rows($list);
            });
        })->export('xls');
    }

    /**
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
    protected function GetTodayList(){
        $today = Carbon::now()->toDateString();
        $list  = Comcheck::with('company')->whereDate('created', $today)->orderBy('id','desc')->get();
//        $list  = Comcheck::with('company','users','comcheckdetail')->orderBy('id','desc')->get();
        return $list;
    }
    /**
     * @param $arr
     * @param int $pid
     * @param int $lev
     * @return array
     */
    protected function getarea($arr,$pid = 0,$lev=1) {
        static $area = array();
        foreach($arr as $k=>$v) {
            if($v['pid'] == $pid) {
                if(!isset($v['level'])) $v['level']='';
                $v['level'] = $lev;
                $area[] = $v;
                self::getarea($arr,$v['id'],$lev+1);
            }
        }
        return $area;
    }
    /**
     * @param Request $request
     * @param $pid
     * @param $type
     */
    public function getRegion(Request $request,$pid,$type)
    {
        $area = Area::where('pid',$pid)->get();
        echo json_encode($area);
    }


    protected function exportWordlist()
    {
        $companyId = 16;
        $company = Company::find($companyId);
        $comcheck = new Comcheck();
        $checkinfo = $comcheck->where(['companyId'=>$companyId,'status'=>1])->first();
        if(!$checkinfo) {
            return redirect('/check/choose/'.$companyId);
        }
        $created = date('Y-m-d',strtotime($checkinfo->created));
        $year = date('Y',strtotime($checkinfo->created));
        $month = date('m',strtotime($checkinfo->created));
        $day = date('d',strtotime($checkinfo->created));
        $standardNegative = $comcheck->getStandardNegative($companyId);
        $productNegative = $comcheck->getProductNegative($companyId);
        $src = public_path().$checkinfo['signature'];
        $templateProcessor =  new TemplateProcessor('./word/word1.docx');
        $comName = $company->comName;
        $data['comName'] = $comName;
        $data['address'] = $company->address;
        $data['year'] = $year;
        $data['month'] = $month;
        $data['day'] = $day;
        $data['archives'] = '';
        $data['danger'] = '';
        $i = 0;
        $j = 0;
        foreach($standardNegative as $k => $v)
        {
            $i++;
            $data['archives'] .= '('.$i.')'.$v['content'].'<w:br />';
        }
        foreach ($productNegative as $k => $v) {
            $j++;
            $data['danger'] .= '('.$j.')'.$v['content'].'<w:br />';
        }
        foreach($data as $k=>$v){
            $templateProcessor->setValue($k,$v);
        }
        $docName = $comName.'检测报告1_' . $created;
        $wordUrl = './word/'.$docName.'.docx';
        $templateProcessor->saveAs($wordUrl);
        $checkinfo->wordsUrl = $wordUrl;
        $checkinfo->save();
        return response()->download($wordUrl);
    }

}
